<!DOCTYPE html>
<html lang="zh-cn">
	<head>
		<meta charset="utf-8">
		<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
		<meta name="viewport" content="width=device-width, initial-scale=1">
		 
			
  
    <meta name="twitter:card" content="summary"/>
    
      <meta name="twitter:image" content="https://www.samrainhan.com/images/avatar.png" />
    
  
  
  <meta name="twitter:title" content="整理SqlServer表上索引碎片"/>
  <meta name="twitter:description" content="查询当前数据库的表上索引碎片情况，按照严重程度顺序排列 USE DBNAME; SELECT DB_NAME() AS DatbaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] INTO #TempFragmentation FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id ="/>
  
  
  
  
    <meta name="twitter:creator" content="@韩雨"/>
  



		
		<meta name="author" content="韩雨">
		<meta name="description" content="学习 &amp;nbsp;&amp;bull;&amp;nbsp; 生活">
		<meta name="generator" content="Hugo 0.52" />
		<title>整理SqlServer表上索引碎片 &middot; sam的小窝</title>
		<link rel="shortcut icon" href="https://www.samrainhan.com/images/favicon.ico">
		<link rel="stylesheet" href="https://www.samrainhan.com/css/style.css">
		<link rel="stylesheet" href="https://www.samrainhan.com/css/highlight.css">

		
		<link rel="stylesheet" href="https://www.samrainhan.com/css/font-awesome.min.css">
		

		
		<link href="https://www.samrainhan.com/index.xml" rel="alternate" type="application/rss+xml" title="sam的小窝" />
		

		
	</head>

    <body>
       <nav class="main-nav">
	
	
		<a href='https://www.samrainhan.com/'> <span class="arrow">←</span>Home</a>
	
	<a href='https://www.samrainhan.com/posts'>Archive</a>
	<a href='https://www.samrainhan.com/tags'>Tags</a>
	<a href='https://www.samrainhan.com/about'>About</a>

	

	
	<a class="cta" href="https://www.samrainhan.com/index.xml">Subscribe</a>
	
</nav>


        <section id="wrapper" class="post">
            <article>
                <header>
                    <h1>
                        整理SqlServer表上索引碎片
                    </h1>
                    <h2 class="headline">
                    Jul 25, 2016 00:00
                    · 306 words
                    · 1 minute read
                      <span class="tags">
                      
                      
                          
                              <a href="https://www.samrainhan.com/tags/sqlserver">SqlServer</a>
                          
                              <a href="https://www.samrainhan.com/tags/%E7%B4%A2%E5%BC%95">索引</a>
                          
                              <a href="https://www.samrainhan.com/tags/%E7%A2%8E%E7%89%87">碎片</a>
                          
                      
                      
                      </span>
                    </h2>
                </header>
                
                  
                    <div id="toc">
                      <nav id="TableOfContents">
<ul>
<li>
<ul>
<li><a href="#查询当前数据库的表上索引碎片情况-按照严重程度顺序排列-a-id-orgheadline28-a">查询当前数据库的表上索引碎片情况，按照严重程度顺序排列<a id="orgheadline28"></a></a></li>
<li><a href="#查询当前数据库的表上从来没有使用过的索引-a-id-orgheadline29-a">查询当前数据库的表上从来没有使用过的索引<a id="orgheadline29"></a></a></li>
<li><a href="#查询当前数据库的表上使用的比较少的索引-a-id-orgheadline30-a">查询当前数据库的表上使用的比较少的索引<a id="orgheadline30"></a></a></li>
<li><a href="#重建索引-对于碎片比较厉害的索引-效果比重组要好-a-id-orgheadline31-a">重建索引，对于碎片比较厉害的索引，效果比重组要好<a id="orgheadline31"></a></a></li>
</ul></li>
</ul>
</nav>
                    </div>
                  
                
                <section id="post-body">
                    

<h2 id="查询当前数据库的表上索引碎片情况-按照严重程度顺序排列-a-id-orgheadline28-a">查询当前数据库的表上索引碎片情况，按照严重程度顺序排列<a id="orgheadline28"></a></h2>

<pre><code>USE DBNAME;
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id;

SELECT * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC;

DROP TABLE #TempFragmentation;
</code></pre>

<h2 id="查询当前数据库的表上从来没有使用过的索引-a-id-orgheadline29-a">查询当前数据库的表上从来没有使用过的索引<a id="orgheadline29"></a></h2>

<pre><code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.name AS IndexName 
INTO #TempNeverUsedIndexes 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id


SELECT * FROM #TempNeverUsedIndexes 
ORDER BY DatbaseName, SchemaName, TableName, IndexName


DROP TABLE #TempNeverUsedIndexes
</code></pre>

<h2 id="查询当前数据库的表上使用的比较少的索引-a-id-orgheadline30-a">查询当前数据库的表上使用的比较少的索引<a id="orgheadline30"></a></h2>

<pre><code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups AS [System USAGE] INTO #TempUnusedIndexes
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id ;
SELECT TOP 20 *
FROM #TempUnusedIndexes ORDER BY [user_updates] DESC ;
DROP TABLE #TempUnusedIndexes
</code></pre>

<h2 id="重建索引-对于碎片比较厉害的索引-效果比重组要好-a-id-orgheadline31-a">重建索引，对于碎片比较厉害的索引，效果比重组要好<a id="orgheadline31"></a></h2>

<pre><code>ALTER INDEX [INDEX_NAME] ON Table_Name REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
</code></pre>

                </section>
            </article>

            
                <a class="twitter" href="https://twitter.com/intent/tweet?text=https%3a%2f%2fwww.samrainhan.com%2fposts%2f2016-07-25-sort-sqlserver-table-index-fragments%2f - %e6%95%b4%e7%90%86SqlServer%e8%a1%a8%e4%b8%8a%e7%b4%a2%e5%bc%95%e7%a2%8e%e7%89%87 "><span class="icon-twitter"> tweet</span></a>

<a class="facebook" href="#" onclick="
    window.open(
      'https://www.facebook.com/sharer/sharer.php?u='+encodeURIComponent(location.href),
      'facebook-share-dialog',
      'width=626,height=436');
    return false;"><span class="icon-facebook-rect"> Share</span>
</a>

            

            
                <div id="disqus_thread"></div>
<script type="text/javascript">
    var disqus_shortname = 'samrainblogbygithub'; 

     
    (function() {
        var dsq = document.createElement('script'); dsq.type = 'text/javascript'; dsq.async = true;
        dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
        (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
    })();
</script>
<noscript>Please enable JavaScript to view the <a href="https://disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript>
</div>

            

            
                <ul id="post-list" class="archive readmore">
    <h3>Read more</h3>

    
    
    
        <li>
            <a href="/posts/2019-04-26-c9-move-to-aws-c9/">Cloud9 Move to Aws C9<aside class="dates">Apr 26 2019</aside></a>
        </li>
    
        <li>
            <a href="/posts/2019-02-16-why-build-blog-with-cloud9/">2019 02 16 Why Build Blog With Cloud9<aside class="dates">Feb 16 2019</aside></a>
        </li>
    
        <li>
            <a href="/posts/2018-09-12-sports-stretching-best-practices/">运动拉伸最佳实践<aside class="dates">Sep 12 2018</aside></a>
        </li>
    
        <li>
            <a href="/posts/2018-09-12-add-2-files-to-tempdb-for-caching/">给tempdb加2个文件做缓存<aside class="dates">Sep 12 2018</aside></a>
        </li>
    
        <li>
            <a href="/posts/2018-09-12-look-for-sql-server-tables-without-a-clustered-index/">如何找到没有聚集索引的表<aside class="dates">Sep 12 2018</aside></a>
        </li>
    
        <li>
            <a href="/posts/2018-09-12-the-continuous-delivery-maturity-model/">持续实施成熟度模型<aside class="dates">Sep 12 2018</aside></a>
        </li>
    
        <li>
            <a href="/posts/2018-09-12-map-of-continuous-delivery/">持续发布的各个阶段<aside class="dates">Sep 12 2018</aside></a>
        </li>
    
        <li>
            <a href="/posts/2018-09-12-how-dropbox-securely-stores-your-passwords/">Dropbox的用户密码加密机制<aside class="dates">Sep 12 2018</aside></a>
        </li>
    
        <li>
            <a href="/posts/2018-09-12-code-inspection-tool-for-net/">NET代码检查工具<aside class="dates">Sep 12 2018</aside></a>
        </li>
    
        <li>
            <a href="/posts/2018-09-12-how-to-make-unique-id/">怎样生成唯一ID<aside class="dates">Sep 12 2018</aside></a>
        </li>
    
</ul>

            

            <footer id="footer">
    
        <div id="social">

	
	
    <a class="symbol" href="">
        <i class="fa fa-facebook-square"></i>
    </a>
    
    <a class="symbol" href="https://github.com/samrain">
        <i class="fa fa-github-square"></i>
    </a>
    
    <a class="symbol" href="">
        <i class="fa fa-twitter-square"></i>
    </a>
    


</div>

    
    <p class="small">
    
       © Copyright 2019 <i class="fa fa-heart" aria-hidden="true"></i> 韩雨
    
    </p>
    <p class="small">
        Powered by <a href="http://www.gohugo.io/">Hugo</a> Theme By <a href="https://github.com/nodejh/hugo-theme-cactus-plus">nodejh</a>
    </p>
</footer>

        </section>

        <script src="https://www.samrainhan.com/js/jquery-3.3.1.min.js"></script>
<script src="https://www.samrainhan.com/js/main.js"></script>
<script src="https://www.samrainhan.com/js/highlight.min.js"></script>
<script>hljs.initHighlightingOnLoad();</script>




  
<script type="application/javascript">
var doNotTrack = false;
if (!doNotTrack) {
	(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
	(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
	m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
	})(window,document,'script','https://www.google-analytics.com/analytics.js','ga');
	ga('create', 'UA-37708730-1', 'auto');
	
	ga('send', 'pageview');
}
</script>





    </body>
</html>
